# coding=utf-8
from framework.db.mysql import BaseDb
from tornado import gen
from lib import log


class Staff(BaseDb):

    @gen.coroutine
    def insert_staff(self, staff_info):

        ret = yield self.insert("tb_staff", staff_info)

        self.commit()

        raise gen.Return(ret)

    @gen.coroutine
    def select_staff(self, school_id, where, page, offset):
        sql = '''SELECT a.Fid, a.Fname, a.Fage, a.Fhire_date, a.Ftel
                , a.Favater, a.Fposition, b.Fdorm_name, a.Fremark, a.Fcreate_time
                FROM tb_staff a 
                LEFT JOIN tb_dorm b 
                ON a.Fdorm_id = b.Fid
                WHERE a.Fschool_id = %s %s 
                ORDER BY a.Fcreate_time DESC
                LIMIT %s, %s'''
        count_sql = 'SELECT count(a.Fid) FROM tb_staff a WHERE a.Fschool_id = %s %s'

        wheres = ''
        for i in where.keys():
            if where[i] != '':
                wheres += " AND a." + i + "='" + str(where[i]) + "'"
        sql = sql % ('%s', wheres, '%s', '%s')
        count_sql = count_sql % ('%s', wheres)

        ret = yield self.executejson(sql, (school_id, page, offset))
        count = yield self.execute(count_sql, (school_id))

        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def find_staff(self, Fid):
        sql = '''SELECT Fid, Fname, Fage, Fhire_date, Ftel
                , Favater, Fposition, Fdorm_id, Fremark, Fcreate_time
                FROM tb_staff 
                WHERE Fid = %s'''

        ret = yield self.executejson(sql, (Fid))

        if len(ret) >= 1:
            raise gen.Return(ret[0])
        else:
            raise gen.Return({})

    @gen.coroutine
    def del_staff(self, Fid):
        sql = 'DELETE FROM tb_staff WHERE Fid = %s'

        ret = yield self.upsert_commit(sql, (Fid))

        raise gen.Return(ret)

    @gen.coroutine
    def update_staff(self, Fid, arg):
        staff_sql = '''UPDATE tb_staff SET %s WHERE Fid = %s'''

        update_data = ''
        for i in arg.keys():
            update_data += i + '=%s,'

        staff_sql = staff_sql % (update_data[0:-1], '%s')

        data = arg.values()
        data.append(Fid)

        ret = yield self.upsert_commit(staff_sql, tuple(data))

        raise gen.Return(ret)
